﻿using System.Data;
using MySqlConnector;

namespace Front._1118_2;
public partial class BookForm : Form
{
    // 提取数据库连接字符串（方便复用）
    private readonly string _connStr = "server=localhost;port=3306;database=book;user=root;password=root;charset=utf8mb4;AllowPublicKeyRetrieval=True";

    public BookForm()
    {
        InitializeComponent();
    }

    private void BookForm_Load(object sender, EventArgs e)
    {
        // 查询图书
        GetAllBooks();
        //查询分类
        GetAllCategories();
    }

    private void GetAllCategories()
    {
        try
        {
            using var connection = new MySqlConnection(_connStr);
            connection.Open();
            string sql = "SELECT distinct book_category FROM book_info;";
            MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sql, connection);
            DataSet dataSet = new DataSet();
            mySqlDataAdapter.Fill(dataSet, "book_category");
            if (dataSet.Tables["book_category"].Rows.Count > 0)
            {
                DataTable categoryTable = dataSet.Tables["book_category"];
                // 插入一行全部
                DataRow row = categoryTable.NewRow();
                row[0] = "全部";
                categoryTable.Rows.InsertAt(row, 0);
            
                // 关键：同时设置DisplayMember和ValueMember（都绑定到分类字段）
                comboBox1.DisplayMember = categoryTable.Columns[0].ColumnName; // 显示的文本
                comboBox1.ValueMember = categoryTable.Columns[0].ColumnName;   // 实际取值的字段
                comboBox1.DataSource = categoryTable;
            }
        }
        catch (Exception ex)
        {
            //MessageBox.Show("加载分类失败：" + ex.Message, "错误", MessageBoxIcon.Error);
        }
    }

    private void GetAllBooks()
    {
        //测试数据库连接
        using var connection = new MySqlConnection("server=localhost;port=3306;database=book;user=root;password=root;charset=utf8mb4;AllowPublicKeyRetrieval=True");
        connection.Open();
        string sql = "SELECT * FROM book_info;";
        MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sql,connection);
        //创建Dataset
        DataSet dataSet = new DataSet();
        mySqlDataAdapter.Fill(dataSet, "book_info");
        this.dataGridView1.DataSource = dataSet.Tables["book_info"];
    }
    
    // 新增：带条件的图书查询方法
    private void GetBooksByCondition(string bookName, string category)
    {
            using var connection = new MySqlConnection(_connStr);
            connection.Open();

            // 构建带参数的SQL语句
            string sql = "SELECT * FROM book_info WHERE 1=1";
            MySqlCommand cmd = new MySqlCommand(sql, connection);

            // 图书名称模糊查询（假设表字段为book_name，根据你的实际字段调整）
            if (!string.IsNullOrEmpty(bookName.Trim()))
            {
                sql += " AND book_name LIKE @BookName";
                cmd.Parameters.AddWithValue("@BookName", "%" + bookName.Trim() + "%");
            }

            // 图书分类筛选（排除“全部”选项）
            if (category != "全部" && !string.IsNullOrEmpty(category))
            {
                sql += " AND book_category = @Category";
                cmd.Parameters.AddWithValue("@Category", category);
            }

            cmd.CommandText = sql;
            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet, "book_info");

            // 绑定查询结果
            dataGridView1.DataSource = dataSet.Tables["book_info"];
            
    }

    private void button1_Click(object sender, EventArgs e)
    {
        // 获取输入的图书名称（文本框命名建议：txtBookName）
        string bookName = txtBookName?.Text ?? "";
        // 获取选中的分类
        string selectedCategory = comboBox1.SelectedValue?.ToString() ?? "全部";
        
        // 执行条件查询
        GetBooksByCondition(bookName, selectedCategory);
    }

    private void button2_Click(object sender, EventArgs e)
    {
        AddBookForm addBookForm = new AddBookForm();
        DialogResult result = addBookForm.ShowDialog();
        if (result == DialogResult.OK)
        {
            GetAllBooks();
        }
    }

    private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
    {
        //获取选中的行
        DataGridViewRow row = this.dataGridView1.SelectedRows[0];
        int bookId = (int)row.Cells[0].Value;
        DialogResult result =  MessageBox.Show("确定删除吗", "警告", MessageBoxButtons.OKCancel);
        if (result == DialogResult.OK)
        {
 
            using var connection = new MySqlConnection("server=localhost;port=3306;database=book;user=root;password=root;charset=utf8mb4;AllowPublicKeyRetrieval=True");
            connection.Open();
            string sql = "delete from book_info where book_id = @book_id";
            using var command = new MySqlCommand(sql, connection);
            //给sql语句变量提供值
           
            command.Parameters.AddWithValue("book_id", bookId);
            int count =  command.ExecuteNonQuery();
            if (count > 0)
            {
                MessageBox.Show("删除成功");
                GetAllBooks();
            }
            else
            {
                MessageBox.Show("删除失败");
            }
        }
    }
}